#!/usr/bin/perl

use DBI;

use Cwd;

$baseurl = getcwd;

print "baseurl: " . $baseurl . "\n";

# local system variables
$connect = $baseurl . '/connect.pl';
require $connect;

# connection for the select
$dbh = DBI->connect("DBI:mysql:database=$db_name;host=$db_host", $db_user, $db_pass) or die "couldn't connect to database: " . DBI->errstr;

foreach $table_prefix ($digital_library, $institution, $consortium) {

$table_name = $table_prefix . '_bibs';

$sth = $dbh->prepare("
CREATE TABLE IF NOT EXISTS $table_name (
  source_record_type text NOT NULL,
  source_record mediumtext NOT NULL,
  local_control_number varchar(255) NOT NULL,
  record_owner varchar(10) NOT NULL,
  owner_level int(1) NOT NULL default '4',
  universal_identifier varchar(255) NOT NULL,
  national_control_number text NOT NULL,
  litform text NOT NULL,
  roles text NOT NULL,
  authors_personal text NOT NULL,
  authors_corporate text NOT NULL,
  authors_conferences text NOT NULL,
  authors_uncontrolled text NOT NULL,
  authors_tocs text NOT NULL,
  subjects_personal text NOT NULL,
  subjects_corporate text NOT NULL,
  subjects_conferences text NOT NULL,
  subjects_titles text NOT NULL,
  subjects_worktitles text NOT NULL,
  subjects_chronological text NOT NULL,
  subjects_topical text NOT NULL,
  subjects_geographic text NOT NULL,
  subjects_genre text NOT NULL,
  topics_facet text NOT NULL,
  geographics_facet text NOT NULL,
  genres_facet text NOT NULL,
  coverage_dates_facet text NOT NULL,
  composition_dates_facet text NOT NULL,
  instrumentations_facet text NOT NULL,
  event_dates_facet text NOT NULL,
  titles_uniform text NOT NULL,
  titles_series text NOT NULL,
  titles_alternate text NOT NULL,
  titles_analytic text NOT NULL,
  titles_tocs text NOT NULL,
  oclc text NOT NULL,
  worktitles text NOT NULL,
  mainheading text NOT NULL,
  uniformtitle text NOT NULL,
  maintitle text NOT NULL,
  maintitle_article text NOT NULL,
  title_proper text NOT NULL,
  gmd text NOT NULL,
  subtitle text NOT NULL,
  statement_of_responsibility text NOT NULL,
  languages text NOT NULL,
  audience text NOT NULL,
  pubplace_facet text NOT NULL,
  tracings_titles_alternate text NOT NULL,
  tracings_titles_analytic text NOT NULL,
  tracings_titles_earlier text NOT NULL,
  tracings_titles_later text NOT NULL,
  tracings_added_authors text NOT NULL,
  tracings_added_worktitles text NOT NULL,
  edition text NOT NULL,
  bib_status text NOT NULL,
  cat_date date NOT NULL default '0000-00-00',
  created_date date NOT NULL default '0000-00-00',
  updated_date date NOT NULL default '0000-00-00',
  isn text NOT NULL,
  mattype text NOT NULL,
  biblevel text NOT NULL,
  composed_mattype text NOT NULL,
  marc007_format text NOT NULL,
  formats text NOT NULL,
  accessmethods text NOT NULL,
  datetype text NOT NULL,
  original_pubdate_start text NOT NULL,
  original_pubdate_end text NOT NULL,
  original_pubdate text NOT NULL,
  original_pubdate_range text NOT NULL,
  original_pubdate_decades text NOT NULL,
  original_pubdate_centuries text NOT NULL,
  reprint_pubdate text NOT NULL,
  imprint text NOT NULL,
  pubplace text NOT NULL,
  publisher text NOT NULL,
  extent text NOT NULL,
  roman_pages text NOT NULL,
  pages text NOT NULL,
  duration text NOT NULL,
  series_traced text NOT NULL,
  series_untraced text NOT NULL,
  notes_public text NOT NULL,
  notes_nonpublic text NOT NULL,
  tocs text NOT NULL,
  urls text NOT NULL,
  lc_callnumber text NOT NULL,
  lc_class text NOT NULL,
  lcc_range text NOT NULL,
  lc_class_label text NOT NULL,
  sudoc_callnumber text NOT NULL,
  sudoc_class text NOT NULL,
  dewey_callnumber text NOT NULL,
  dewey_class text NOT NULL,
  other_callnumber text NOT NULL,
  other_class text NOT NULL,
  local_callnumber text NOT NULL,
  local_class text NOT NULL,
  call_numbers text NOT NULL,
  bloc text NOT NULL,
  donors text NOT NULL,
  collections text NOT NULL,
  unaccounted_marc_fields text NOT NULL,
  marc_errors text NOT NULL,
  solr_update timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  UNIQUE KEY local_control_number (local_control_number),
  KEY bib_status (bib_status(2)),
  KEY bloc (bloc(255)),
  KEY maintitle (maintitle_article(30)),
  KEY maintitle_article (maintitle(9)),
  KEY oclc (oclc(25)),
  KEY record_owner (record_owner),
  KEY owner_level (owner_level),
  KEY universal_identifier (universal_identifier),
  KEY cat_date (cat_date),
  KEY worktitles (worktitles(255)),
  KEY solr_update (solr_update)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 PACK_KEYS=0 ;
");

$sth->execute;

}

foreach $table_prefix ($institution, $consortium) {

$table_name = $table_prefix . '_items';

$sth = $dbh->prepare("
CREATE TABLE IF NOT EXISTS $table_name (
  item_record varchar(15) NOT NULL,
  item_sequence int(11) NOT NULL default '0',
  item_created date NOT NULL default '0000-00-00',
  item_updated date NOT NULL default '0000-00-00',
  local_control_number varchar(255) NOT NULL,
  universal_identifier varchar(255) NOT NULL,
  record_owner varchar(10) NOT NULL,
  item_iloc varchar(11) NOT NULL,
  labelloc text NOT NULL,
  volume text NOT NULL,
  copy int(2) NOT NULL,
  barcode text NOT NULL,
  item_callnumber text NOT NULL,
  item_lc_class text NOT NULL,
  pub_year varchar(4) NOT NULL default '0',
  outdate date NOT NULL default '0000-00-00',
  duedate date NOT NULL default '0000-00-00',
  oduedate date NOT NULL default '0000-00-00',
  recaldate date NOT NULL default '0000-00-00',
  lchkin date NOT NULL default '0000-00-00',
  tot_chkout text NOT NULL,
  tot_renew text NOT NULL,
  intl_use text NOT NULL,
  save_item text NOT NULL,
  notes text NOT NULL,
  circ_status text NOT NULL,
  icode1 text NOT NULL,
  item_status text NOT NULL,
  itype text NOT NULL,
  loanrule text NOT NULL,
  course_id text NOT NULL,
  solr_update timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  UNIQUE KEY item_local_control_number (item_record,local_control_number),
  KEY item_status (item_status(1)),
  KEY circ_status (circ_status(1)),
  KEY item_iloc (item_iloc),
  KEY local_control_number (local_control_number),
  KEY universal_identifier (universal_identifier),
  KEY record_owner (record_owner),
  KEY solr_update (solr_update)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 PACK_KEYS=0 ;
");

$sth->execute;

$table_name = $table_prefix . '_orders';

$sth = $dbh->prepare("
CREATE TABLE IF NOT EXISTS $table_name (
  order_record varchar(15) NOT NULL,
  order_created date NOT NULL default '0000-00-00',
  order_updated date NOT NULL default '0000-00-00',
  order_date date NOT NULL default '0000-00-00',
  received_date date NOT NULL default '0000-00-00',
  cat_date date NOT NULL default '0000-00-00',
  order2receipt_time int(11) NOT NULL default '0',
  receipt2catalog_time int(11) NOT NULL default '0',
  cat_agency text NOT NULL,
  local_control_number varchar(255) NOT NULL,
  record_owner varchar(10) NOT NULL,
  universal_identifier varchar(255) NOT NULL,
  oloc varchar(5) NOT NULL,
  rloc varchar(5) NOT NULL,
  bloc varchar(5) NOT NULL,
  order_callnumber text NOT NULL,
  order_lc_class_a text NOT NULL,
  order_lc_class_ab text NOT NULL,
  order_status text NOT NULL,
  acq_type text NOT NULL,
  order_type text NOT NULL,
  form text NOT NULL,
  fund text NOT NULL,
  vendor text NOT NULL,
  solr_update timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  UNIQUE KEY order_local_control_number (order_record,local_control_number),
  KEY order_status (order_status(1)),
  KEY local_control_number (local_control_number),
  KEY universal_identifier (universal_identifier),
  KEY record_owner (record_owner),
  KEY solr_update (solr_update)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
");

$sth->execute;

$table_name = $table_prefix . '_holdings';

$sth = $dbh->prepare("
CREATE TABLE IF NOT EXISTS $table_name (
  local_control_number varchar(255) NOT NULL,
  record_owner varchar(10) NOT NULL,
  universal_identifier varchar(255) NOT NULL,
  holding_status varchar(1) NOT NULL default '-',
  formats text NOT NULL,
  hloc varchar(11) NOT NULL,
  lib_has text NOT NULL,
  latest_received text NOT NULL,
  solr_update timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  UNIQUE KEY local_control_number_hloc (local_control_number,hloc),
  KEY universal_identifier (universal_identifier),
  KEY holding_status (holding_status),
  KEY record_owner (record_owner),
  KEY solr_update (solr_update),
  KEY hloc (hloc)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 PACK_KEYS=0;
");

$sth->execute;

$table_name = $table_prefix . '_checkins';

$sth = $dbh->prepare("
CREATE TABLE IF NOT EXISTS $table_name (
  checkin_record varchar(15) NOT NULL,
  scode1 text NOT NULL,
  checkin_status varchar(1) NOT NULL,
  cloc varchar(11) NOT NULL,
  rloc text NOT NULL,
  checkin_created date NOT NULL default '0000-00-00',
  checkin_updated date NOT NULL default '0000-00-00',
  latest_received text NOT NULL,
  ckin_info text NOT NULL,
  erm_link text NOT NULL,
  lib_has text NOT NULL,
  checkin_callnumber text NOT NULL,
  local_control_number varchar(255) NOT NULL,
  universal_identifier varchar(255) NOT NULL,
  record_owner varchar(10) NOT NULL,
  solr_update timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  UNIQUE KEY checkin_record_cloc (checkin_record,cloc),
  KEY local_control_number (local_control_number),
  KEY universal_identifier (universal_identifier),
  KEY record_owner (record_owner),
  KEY checkin_status (checkin_status),
  KEY solr_update (solr_update)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 PACK_KEYS=0 ;
");

$sth->execute;

$table_name = $table_prefix . '_courses';

$sth = $dbh->prepare("
CREATE TABLE IF NOT EXISTS $table_name (
  course_record varchar(15) NOT NULL,
  date_begin date NOT NULL default '0000-00-00',
  date_end date NOT NULL default '0000-00-00',
  course_loc varchar(11) NOT NULL,
  ccode1 text NOT NULL,
  ccode2 text NOT NULL,
  ccode3 text NOT NULL,
  course_created date NOT NULL default '0000-00-00',
  course_updated date NOT NULL default '0000-00-00',
  revisions text NOT NULL,
  prof_names text NOT NULL,
  course_names text NOT NULL,
  course_notes text NOT NULL,
  course_status varchar(1) NOT NULL,
  item_record varchar(15) NOT NULL,
  local_control_number varchar(255) NOT NULL,
  universal_identifier varchar(255) NOT NULL,
  record_owner varchar(10) NOT NULL,
  solr_update timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  UNIQUE KEY course_record_item_record (course_record, item_record),
  KEY universal_identifier (universal_identifier),
  KEY record_owner (record_owner),
  KEY course_status (course_status),
  KEY solr_update (solr_update),
  KEY local_control_number (local_control_number)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 PACK_KEYS=0;
");

$sth->execute;

$table_name = $table_prefix . '_locations';

$sth = $dbh->prepare("
CREATE TABLE IF NOT EXISTS $table_name (
  iloc varchar(11) NOT NULL,
  bloc text,
  iloc_name text,
  bloc_name text,
  campus_name text,
  swordloc text NOT NULL,
  field_049 varchar(4) NOT NULL,
  labelloc text,
  status varchar(1) NOT NULL,
  itype varchar(3) NOT NULL,
  icode1 varchar(3) NOT NULL,
  imessage text,
  callnumber text,
  explanation text,
  previous_iloc_name text,
  to_be_reviewed text,
  iloc_status text,
  numberofitems int(11) NOT NULL default '0',
  maps text,
solr_update timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  UNIQUE KEY iloc (iloc)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 PACK_KEYS=0 ;
");

$sth->execute;

$table_name = $table_prefix . '_authorities';

$sth = $dbh->prepare("
CREATE TABLE IF NOT EXISTS $table_name (
  source_record_type text NOT NULL,
  source_record mediumtext NOT NULL,
  local_control_number varchar(255) NOT NULL,
  record_owner varchar(10) NOT NULL,
  owner_level int(1) NOT NULL default '4',
  universal_identifier varchar(255) NOT NULL,
  national_control_number varchar(30) NOT NULL,
  geo_subdivide text NOT NULL,
  name_use text NOT NULL,
  subj_use text NOT NULL,
  series_use text NOT NULL,
  mainheading text NOT NULL,
  index_name text NOT NULL,
  heading_raw text NOT NULL,
  authors_personal_invalid text NOT NULL,
  authors_corporate_invalid text NOT NULL,
  authors_conferences_invalid text NOT NULL,
  subjects_personal_invalid text NOT NULL,
  subjects_corporate_invalid text NOT NULL,
  subjects_conferences_invalid text NOT NULL,
  subjects_titles_invalid text NOT NULL,
  subjects_chronological_invalid text NOT NULL,
  subjects_topical_invalid text NOT NULL,
  subjects_geographic_invalid text NOT NULL,
  subjects_genre_invalid text NOT NULL,
  titles_uniform_invalid text NOT NULL,
  authors_personal_broader text NOT NULL,
  authors_corporate_broader text NOT NULL,
  authors_conferences_broader text NOT NULL,
  subjects_personal_broader text NOT NULL,
  subjects_corporate_broader text NOT NULL,
  subjects_conferences_broader text NOT NULL,
  subjects_titles_broader text NOT NULL,
  subjects_chronological_broader text NOT NULL,
  subjects_topical_broader text NOT NULL,
  subjects_geographic_broader text NOT NULL,
  subjects_genre_broader text NOT NULL,
  titles_uniform_broader text NOT NULL,
  authors_personal_related text NOT NULL,
  authors_corporate_related text NOT NULL,
  authors_conferences_related text NOT NULL,
  subjects_personal_related text NOT NULL,
  subjects_corporate_related text NOT NULL,
  subjects_conferences_related text NOT NULL,
  subjects_titles_related text NOT NULL,
  subjects_chronological_related text NOT NULL,
  subjects_topical_related text NOT NULL,
  subjects_geographic_related text NOT NULL,
  subjects_genre_related text NOT NULL,
  titles_uniform_related text NOT NULL,
  isn text NOT NULL,
  oclc_arn text NOT NULL,
  worktitles text NOT NULL,
  languages text NOT NULL,
  auth_status text NOT NULL,
  created_date date NOT NULL default '0000-00-00',
  updated_date date NOT NULL default '0000-00-00',
  source_data text NOT NULL,
  source_nondata text NOT NULL,
  notes_public text NOT NULL,
  notes_nonpublic text NOT NULL,
  urls text NOT NULL,
  lc_callnumber text NOT NULL,
  lc_class text NOT NULL,
  lcc_range text NOT NULL,
  lc_class_label text NOT NULL,
  sudoc_callnumber text NOT NULL,
  sudoc_class text NOT NULL,
  dewey_callnumber text NOT NULL,
  dewey_class text NOT NULL,
  other_callnumber text NOT NULL,
  other_class text NOT NULL,
  local_callnumber text NOT NULL,
  local_class text NOT NULL,
  call_numbers text NOT NULL,
  unaccounted_marc_fields text NOT NULL,
  marc_errors text NOT NULL,
  solr_update timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  UNIQUE KEY local_control_number_national_control_number (local_control_number,national_control_number),
  KEY auth_status (auth_status(2)),
  KEY oclc_arn (oclc_arn(25)),
  KEY record_owner (record_owner),
  KEY owner_level (owner_level),
  KEY solr_update (solr_update),
  KEY heading_raw (heading_raw(255)),
  KEY index_name (index_name(25)),
  KEY worktitles (worktitles(255))
) ENGINE=MyISAM DEFAULT CHARSET=utf8 PACK_KEYS=0;
");

$sth->execute;

$table_name = $table_prefix . '_authorities_hierarchies';

$sth = $dbh->prepare("
CREATE TABLE IF NOT EXISTS $table_name (
  term text NOT NULL,
  broader text NOT NULL,
  expansion text NOT NULL,
  KEY term (term(255))
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
");

$sth->execute;

$table_name = $table_prefix . '_authorities_hierarchies_trees';

$sth = $dbh->prepare("
CREATE TABLE IF NOT EXISTS $table_name (
  id int(11) NOT NULL auto_increment,
  term text NOT NULL,
  broader01 text NOT NULL,
  broader02 text NOT NULL,
  broader03 text NOT NULL,
  broader04 text NOT NULL,
  broader05 text NOT NULL,
  broader06 text NOT NULL,
  broader07 text NOT NULL,
  broader08 text NOT NULL,
  broader09 text NOT NULL,
  broader10 text NOT NULL,
  broader11 text NOT NULL,
  broader12 text NOT NULL,
  broader13 text NOT NULL,
  broader14 text NOT NULL,
  broader15 text NOT NULL,
  broader16 text NOT NULL,
  broader17 text NOT NULL,
  broader18 text NOT NULL,
  broader19 text NOT NULL,
  broader20 text NOT NULL,
  PRIMARY KEY  (id),
  KEY term (term(255))
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
");

$sth->execute;

}

foreach $table_prefix ($digital_library, $institution) {

$table_name = $table_prefix . '_bibs_temp';

$sth = $dbh->prepare("
CREATE TABLE IF NOT EXISTS $table_name (
  source_record_type text NOT NULL,
  source_record mediumtext NOT NULL,
  local_control_number varchar(255) NOT NULL,
  record_owner varchar(10) NOT NULL,
  owner_level int(1) NOT NULL default '4',
  universal_identifier varchar(255) NOT NULL,
  national_control_number text NOT NULL,
  litform text NOT NULL,
  roles text NOT NULL,
  authors_personal text NOT NULL,
  authors_corporate text NOT NULL,
  authors_conferences text NOT NULL,
  authors_uncontrolled text NOT NULL,
  authors_tocs text NOT NULL,
  subjects_personal text NOT NULL,
  subjects_corporate text NOT NULL,
  subjects_conferences text NOT NULL,
  subjects_titles text NOT NULL,
  subjects_worktitles text NOT NULL,
  subjects_chronological text NOT NULL,
  subjects_topical text NOT NULL,
  subjects_geographic text NOT NULL,
  subjects_genre text NOT NULL,
  topics_facet text NOT NULL,
  geographics_facet text NOT NULL,
  genres_facet text NOT NULL,
  coverage_dates_facet text NOT NULL,
  composition_dates_facet text NOT NULL,
  instrumentations_facet text NOT NULL,
  event_dates_facet text NOT NULL,
  titles_uniform text NOT NULL,
  titles_series text NOT NULL,
  titles_alternate text NOT NULL,
  titles_analytic text NOT NULL,
  titles_tocs text NOT NULL,
  oclc text NOT NULL,
  worktitles text NOT NULL,
  mainheading text NOT NULL,
  uniformtitle text NOT NULL,
  maintitle text NOT NULL,
  maintitle_article text NOT NULL,
  title_proper text NOT NULL,
  gmd text NOT NULL,
  subtitle text NOT NULL,
  statement_of_responsibility text NOT NULL,
  languages text NOT NULL,
  audience text NOT NULL,
  pubplace_facet text NOT NULL,
  tracings_titles_alternate text NOT NULL,
  tracings_titles_analytic text NOT NULL,
  tracings_titles_earlier text NOT NULL,
  tracings_titles_later text NOT NULL,
  tracings_added_authors text NOT NULL,
  tracings_added_worktitles text NOT NULL,
  edition text NOT NULL,
  bib_status text NOT NULL,
  cat_date date NOT NULL default '0000-00-00',
  created_date date NOT NULL default '0000-00-00',
  updated_date date NOT NULL default '0000-00-00',
  isn text NOT NULL,
  mattype text NOT NULL,
  biblevel text NOT NULL,
  composed_mattype text NOT NULL,
  marc007_format text NOT NULL,
  formats text NOT NULL,
  accessmethods text NOT NULL,
  datetype text NOT NULL,
  original_pubdate_start text NOT NULL,
  original_pubdate_end text NOT NULL,
  original_pubdate text NOT NULL,
  original_pubdate_range text NOT NULL,
  original_pubdate_decades text NOT NULL,
  original_pubdate_centuries text NOT NULL,
  reprint_pubdate text NOT NULL,
  imprint text NOT NULL,
  pubplace text NOT NULL,
  publisher text NOT NULL,
  extent text NOT NULL,
  roman_pages text NOT NULL,
  pages text NOT NULL,
  duration text NOT NULL,
  series_traced text NOT NULL,
  series_untraced text NOT NULL,
  notes_public text NOT NULL,
  notes_nonpublic text NOT NULL,
  tocs text NOT NULL,
  urls text NOT NULL,
  lc_callnumber text NOT NULL,
  lc_class text NOT NULL,
  lcc_range text NOT NULL,
  lc_class_label text NOT NULL,
  sudoc_callnumber text NOT NULL,
  sudoc_class text NOT NULL,
  dewey_callnumber text NOT NULL,
  dewey_class text NOT NULL,
  other_callnumber text NOT NULL,
  other_class text NOT NULL,
  local_callnumber text NOT NULL,
  local_class text NOT NULL,
  call_numbers text NOT NULL,
  bloc text NOT NULL,
  donors text NOT NULL,
  collections text NOT NULL,
  unaccounted_marc_fields text NOT NULL,
  marc_errors text NOT NULL,
  solr_update timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  UNIQUE KEY local_control_number (local_control_number),
  KEY bib_status (bib_status(2)),
  KEY bloc (bloc(255)),
  KEY maintitle (maintitle_article(30)),
  KEY maintitle_article (maintitle(9)),
  KEY oclc (oclc(25)),
  KEY record_owner (record_owner),
  KEY owner_level (owner_level),
  KEY universal_identifier (universal_identifier),
  KEY cat_date (cat_date),
  KEY solr_update (solr_update)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 PACK_KEYS=0 ;
");

$sth->execute;

}

foreach $table_prefix ($institution) {
$table_name = $table_prefix . '_items_temp';

$sth = $dbh->prepare("
CREATE TABLE IF NOT EXISTS $table_name (
  item_record varchar(15) NOT NULL,
  item_sequence int(11) NOT NULL default '0',
  item_created date NOT NULL default '0000-00-00',
  item_updated date NOT NULL default '0000-00-00',
  local_control_number varchar(255) NOT NULL,
  universal_identifier varchar(255) NOT NULL,
  record_owner varchar(10) NOT NULL,
  item_iloc varchar(11) NOT NULL,
  labelloc text NOT NULL,
  volume text NOT NULL,
  copy int(2) NOT NULL,
  barcode text NOT NULL,
  item_callnumber text NOT NULL,
  item_lc_class text NOT NULL,
  pub_year varchar(4) NOT NULL default '0',
  outdate date NOT NULL default '0000-00-00',
  duedate date NOT NULL default '0000-00-00',
  oduedate date NOT NULL default '0000-00-00',
  recaldate date NOT NULL default '0000-00-00',
  lchkin date NOT NULL default '0000-00-00',
  tot_chkout text NOT NULL,
  tot_renew text NOT NULL,
  intl_use text NOT NULL,
  save_item text NOT NULL,
  notes text NOT NULL,
  circ_status text NOT NULL,
  icode1 text NOT NULL,
  item_status text NOT NULL,
  itype text NOT NULL,
  loanrule text NOT NULL,
  course_id text NOT NULL,
  solr_update timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  UNIQUE KEY item_local_control_number (item_record,local_control_number),
  KEY item_status (item_status(1)),
  KEY circ_status (circ_status(1)),
  KEY item_iloc (item_iloc),
  KEY local_control_number (local_control_number),
  KEY universal_identifier (universal_identifier),
  KEY record_owner (record_owner),
  KEY solr_update (solr_update)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 PACK_KEYS=0 ;
");

$sth->execute;

$table_name = $table_prefix . '_items_sequences';

$sth = $dbh->prepare("
CREATE TABLE IF NOT EXISTS $table_name (
  item_record varchar(15) NOT NULL,
  local_control_number varchar(255) NOT NULL,
  item_sequence int(11) NOT NULL,
  solr_update timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  UNIQUE KEY item_local_control_number (item_record,local_control_number),
  KEY local_control_number (local_control_number),
  KEY solr_update (solr_update)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 PACK_KEYS=0 ;
");

$sth->execute;

$table_name = $table_prefix . '_checkins_temp';

$sth = $dbh->prepare("
CREATE TABLE IF NOT EXISTS $table_name (
  checkin_record varchar(15) NOT NULL,
  scode1 text NOT NULL,
  checkin_status varchar(1) NOT NULL,
  cloc varchar(11) NOT NULL,
  rloc text NOT NULL,
  checkin_created date NOT NULL default '0000-00-00',
  checkin_updated date NOT NULL default '0000-00-00',
  latest_received text NOT NULL,
  ckin_info text NOT NULL,
  erm_link text NOT NULL,
  lib_has text NOT NULL,
  checkin_callnumber text NOT NULL,
  local_control_number varchar(255) NOT NULL,
  universal_identifier varchar(255) NOT NULL,
  record_owner varchar(10) NOT NULL,
  solr_update timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  UNIQUE KEY checkin_record_cloc (checkin_record,cloc),
  KEY local_control_number (local_control_number),
  KEY universal_identifier (universal_identifier),
  KEY record_owner (record_owner),
  KEY checkin_status (checkin_status),
  KEY solr_update (solr_update)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 PACK_KEYS=0 ;
");

$sth->execute;

$table_name = $table_prefix . '_courses_temp';

$sth = $dbh->prepare("
CREATE TABLE IF NOT EXISTS $table_name (
  course_record varchar(15) NOT NULL,
  date_begin date NOT NULL default '0000-00-00',
  date_end date NOT NULL default '0000-00-00',
  course_loc varchar(11) NOT NULL,
  ccode1 text NOT NULL,
  ccode2 text NOT NULL,
  ccode3 text NOT NULL,
  course_created date NOT NULL default '0000-00-00',
  course_updated date NOT NULL default '0000-00-00',
  revisions text NOT NULL,
  prof_names text NOT NULL,
  course_names text NOT NULL,
  course_notes text NOT NULL,
  course_status varchar(1) NOT NULL,
  item_record varchar(15) NOT NULL,
  local_control_number varchar(255) NOT NULL,
  universal_identifier varchar(255) NOT NULL,
  record_owner varchar(10) NOT NULL,
  solr_update timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  UNIQUE KEY course_record_item_record (course_record, item_record),
  KEY universal_identifier (universal_identifier),
  KEY record_owner (record_owner),
  KEY course_status (course_status),
  KEY solr_update (solr_update),
  KEY local_control_number (local_control_number)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 PACK_KEYS=0;
");

$sth->execute;


$table_name = $table_prefix . '_orders_temp';

$sth = $dbh->prepare("
CREATE TABLE IF NOT EXISTS $table_name (
  order_record varchar(15) NOT NULL,
  order_created date NOT NULL default '0000-00-00',
  order_updated date NOT NULL default '0000-00-00',
  order_date date NOT NULL default '0000-00-00',
  received_date date NOT NULL default '0000-00-00',
  cat_date date NOT NULL default '0000-00-00',
  order2receipt_time int(11) NOT NULL default '0',
  receipt2catalog_time int(11) NOT NULL default '0',
  cat_agency text NOT NULL,
  local_control_number varchar(255) NOT NULL,
  record_owner varchar(10) NOT NULL,
  universal_identifier varchar(255) NOT NULL,
  oloc varchar(5) NOT NULL,
  rloc varchar(5) NOT NULL,
  bloc varchar(5) NOT NULL,
  order_callnumber text NOT NULL,
  order_lc_class_a text NOT NULL,
  order_lc_class_ab text NOT NULL,
  order_status text NOT NULL,
  acq_type text NOT NULL,
  order_type text NOT NULL,
  form text NOT NULL,
  fund text NOT NULL,
  vendor text NOT NULL,
  solr_update timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  UNIQUE KEY order_local_control_number (order_record,local_control_number),
  KEY order_status (order_status(1)),
  KEY local_control_number (local_control_number),
  KEY universal_identifier (universal_identifier),
  KEY record_owner (record_owner),
  KEY solr_update (solr_update)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
");

$sth->execute;

$table_name = $table_prefix . '_universal_ids';

$sth = $dbh->prepare("
CREATE TABLE IF NOT EXISTS $table_name (
  local_control_number varchar(255) NOT NULL,
  universal_identifier varchar(255) NOT NULL,
  true_oclc text NOT NULL,
  solr_update timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  UNIQUE KEY local_control_number (local_control_number),
  UNIQUE KEY universal_identifier (universal_identifier),
  KEY true_oclc (true_oclc(50)),
  KEY solr_update (solr_update)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 PACK_KEYS=0 ;
");

$sth->execute;

$table_name = $table_prefix . '_lc_classes';

$sth = $dbh->prepare("
CREATE TABLE IF NOT EXISTS $table_name (
  alpha_start text NOT NULL,
  class_start text NOT NULL,
  alpha_end text NOT NULL,
  class_end text NOT NULL,
  definition text NOT NULL,
  range1 text NOT NULL,
  range2 text NOT NULL,
  UNIQUE KEY range (range1(23),range2(23))
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
");

$sth->execute;

$table_name = $table_prefix . '_authorities_temp';

$sth = $dbh->prepare("
CREATE TABLE IF NOT EXISTS $table_name (
  source_record_type text NOT NULL,
  source_record mediumtext NOT NULL,
  local_control_number varchar(255) NOT NULL,
  record_owner varchar(10) NOT NULL,
  owner_level int(1) NOT NULL default '4',
  universal_identifier varchar(255) NOT NULL,
  national_control_number varchar(30) NOT NULL,
  geo_subdivide text NOT NULL,
  name_use text NOT NULL,
  subj_use text NOT NULL,
  series_use text NOT NULL,
  mainheading text NOT NULL,
  index_name text NOT NULL,
  heading_raw text NOT NULL,
  authors_personal_invalid text NOT NULL,
  authors_corporate_invalid text NOT NULL,
  authors_conferences_invalid text NOT NULL,
  subjects_personal_invalid text NOT NULL,
  subjects_corporate_invalid text NOT NULL,
  subjects_conferences_invalid text NOT NULL,
  subjects_titles_invalid text NOT NULL,
  subjects_chronological_invalid text NOT NULL,
  subjects_topical_invalid text NOT NULL,
  subjects_geographic_invalid text NOT NULL,
  subjects_genre_invalid text NOT NULL,
  titles_uniform_invalid text NOT NULL,
  authors_personal_broader text NOT NULL,
  authors_corporate_broader text NOT NULL,
  authors_conferences_broader text NOT NULL,
  subjects_personal_broader text NOT NULL,
  subjects_corporate_broader text NOT NULL,
  subjects_conferences_broader text NOT NULL,
  subjects_titles_broader text NOT NULL,
  subjects_chronological_broader text NOT NULL,
  subjects_topical_broader text NOT NULL,
  subjects_geographic_broader text NOT NULL,
  subjects_genre_broader text NOT NULL,
  titles_uniform_broader text NOT NULL,
  authors_personal_related text NOT NULL,
  authors_corporate_related text NOT NULL,
  authors_conferences_related text NOT NULL,
  subjects_personal_related text NOT NULL,
  subjects_corporate_related text NOT NULL,
  subjects_conferences_related text NOT NULL,
  subjects_titles_related text NOT NULL,
  subjects_chronological_related text NOT NULL,
  subjects_topical_related text NOT NULL,
  subjects_geographic_related text NOT NULL,
  subjects_genre_related text NOT NULL,
  titles_uniform_related text NOT NULL,
  isn text NOT NULL,
  oclc_arn text NOT NULL,
  worktitles text NOT NULL,
  languages text NOT NULL,
  auth_status text NOT NULL,
  created_date date NOT NULL default '0000-00-00',
  updated_date date NOT NULL default '0000-00-00',
  source_data text NOT NULL,
  source_nondata text NOT NULL,
  notes_public text NOT NULL,
  notes_nonpublic text NOT NULL,
  urls text NOT NULL,
  lc_callnumber text NOT NULL,
  lc_class text NOT NULL,
  lcc_range text NOT NULL,
  lc_class_label text NOT NULL,
  sudoc_callnumber text NOT NULL,
  sudoc_class text NOT NULL,
  dewey_callnumber text NOT NULL,
  dewey_class text NOT NULL,
  other_callnumber text NOT NULL,
  other_class text NOT NULL,
  local_callnumber text NOT NULL,
  local_class text NOT NULL,
  call_numbers text NOT NULL,
  unaccounted_marc_fields text NOT NULL,
  marc_errors text NOT NULL,
  solr_update timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  UNIQUE KEY local_control_number_national_control_number (local_control_number,national_control_number),
  KEY auth_status (auth_status(2)),
  KEY oclc_arn (oclc_arn(25)),
  KEY record_owner (record_owner),
  KEY owner_level (owner_level),
  KEY solr_update (solr_update),
  KEY heading_raw (heading_raw(255)),
  KEY index_name (index_name(25)),
  KEY worktitles (worktitles(255))
) ENGINE=MyISAM DEFAULT CHARSET=utf8 PACK_KEYS=0;
");

$sth->execute;

$table_name = $table_prefix . '_tracings';

$sth = $dbh->prepare("
CREATE TABLE IF NOT EXISTS $table_name (
  local_control_number varchar(255) NOT NULL,
  record_owner varchar(10) NOT NULL,
  owner_level int(1) NOT NULL default '4',
  tracing_sequence int(11) NOT NULL default '0',
  field_tag text NOT NULL,
  first_indicator text NOT NULL,
  second_indicator text NOT NULL,
  mainheading text NOT NULL,
  index_name text NOT NULL,
  roles text NOT NULL,
  thesaurus text NOT NULL,
  institution_to_which_this_applies text NOT NULL,
  heading_raw text NOT NULL,
  volumes text NOT NULL,
  national_control_number text NOT NULL,
  solr_update timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  UNIQUE KEY local_control_number_tracing_sequence (local_control_number, tracing_sequence),
  KEY national_control_number (national_control_number(30)),
  KEY record_owner (record_owner),
  KEY owner_level (owner_level),
  KEY index_name (index_name(25)),
  KEY heading_raw (heading_raw(255)),
  KEY solr_update (solr_update)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 PACK_KEYS=0;
");

$sth->execute;

$table_name = $table_prefix . '_tocs_970s';

$sth = $dbh->prepare("
CREATE TABLE IF NOT EXISTS $table_name (
  local_control_number varchar(255) NOT NULL,
  authors_tocs text NOT NULL,
  titles_tocs text NOT NULL,
  tocs text NOT NULL,
  tocs_update timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  UNIQUE KEY local_control_number (local_control_number)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
");

$sth->execute;

}

foreach $table_prefix ($institution) {

$table_name = $table_prefix . '_lcsh_pref_label';

$sth = $dbh->prepare("
CREATE TABLE IF NOT EXISTS $table_name (
  national_control_number varchar(30) NOT NULL,
  mainheading text NOT NULL,
  index_name text NOT NULL,
  solr_update timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  KEY national_control_number (national_control_number),
  KEY index_name (index_name(25)),
  KEY mainheading (mainheading(255)),
  KEY solr_update (solr_update)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 PACK_KEYS=0;
");

$sth->execute;

}

foreach $table_prefix ($institution) {

$table_name = $table_prefix . '_lcsh_pref_label_temp';

$sth = $dbh->prepare("
CREATE TABLE IF NOT EXISTS $table_name (
  national_control_number varchar(30) NOT NULL,
  mainheading text NOT NULL,
  index_name text NOT NULL,
  solr_update timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  KEY national_control_number (national_control_number),
  KEY index_name (index_name(25)),
  KEY mainheading (mainheading(255)),
  KEY solr_update (solr_update)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 PACK_KEYS=0;
");

$sth->execute;

}

foreach $table_prefix ($institution) {

foreach $table_suffix (_lcsh_alt_label, _lcsh_broader_label, _lcsh_narrower_label) {

$table_name = $table_prefix . $table_suffix;

$sth = $dbh->prepare("
CREATE TABLE IF NOT EXISTS $table_name (
  national_control_number varchar(30) NOT NULL,
  mainheading text NOT NULL,
  solr_update timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  KEY national_control_number (national_control_number),
  KEY mainheading (mainheading(255)),
  KEY solr_update (solr_update)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 PACK_KEYS=0;
");

$sth->execute;

}

}

foreach $table_prefix ($digital_library) {

$table_name = $table_prefix . '_items';

$sth = $dbh->prepare("
CREATE TABLE IF NOT EXISTS $table_name (
  volume_identifier varchar(255) NOT NULL,
  access text NOT NULL,
  rights text NOT NULL,
  local_control_number varchar(255) NOT NULL,
  universal_identifier varchar(255) NOT NULL,
  volume text NOT NULL,
  source text NOT NULL,
  source_record_no text NOT NULL,
  oclc text NOT NULL,
  isbn text NOT NULL,
  issn text NOT NULL,
  lccn text NOT NULL,
  title text NOT NULL,
  imprint text NOT NULL,
  rights_determination_reason_code text NOT NULL,
  date_updated date NOT NULL default '0000-00-00',
  solr_update timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  UNIQUE KEY volume_identifier (volume_identifier),
  KEY universal_identifier (universal_identifier),
  KEY access (access(5)),
  KEY local_control_number (local_control_number),
  KEY date_updated (date_updated),
  KEY solr_update (solr_update),
  KEY oclc (oclc(25))
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
");

$sth->execute;

$table_name = $table_prefix . '_items_temp';

$sth = $dbh->prepare("
CREATE TABLE IF NOT EXISTS $table_name (
  volume_identifier varchar(255) NOT NULL,
  access text NOT NULL,
  rights text NOT NULL,
  local_control_number varchar(255) NOT NULL,
  universal_identifier varchar(255) NOT NULL,
  volume text NOT NULL,
  source text NOT NULL,
  source_record_no text NOT NULL,
  oclc text NOT NULL,
  isbn text NOT NULL,
  issn text NOT NULL,
  lccn text NOT NULL,
  title text NOT NULL,
  imprint text NOT NULL,
  rights_determination_reason_code text NOT NULL,
  date_updated date NOT NULL default '0000-00-00',
  solr_update timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  UNIQUE KEY volume_identifier (volume_identifier),
  KEY universal_identifier (universal_identifier),
  KEY access (access(5)),
  KEY local_control_number (local_control_number),
  KEY date_updated (date_updated),
  KEY solr_update (solr_update),
  KEY oclc (oclc(25))
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
");

$sth->execute;

$table_name = $table_prefix . '_holdings';

$sth = $dbh->prepare("
CREATE TABLE IF NOT EXISTS $table_name (
  local_control_number varchar(255) NOT NULL,
  record_owner varchar(10) NOT NULL,
  universal_identifier varchar(255) NOT NULL,
  holding_status varchar(1) NOT NULL default '-',
  formats text NOT NULL,
  hloc varchar(11) NOT NULL,
  lib_has text NOT NULL,
  latest_received text NOT NULL,
  solr_update timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  UNIQUE KEY local_control_number_hloc (local_control_number,hloc),
  KEY universal_identifier (universal_identifier),
  KEY holding_status (holding_status),
  KEY record_owner (record_owner),
  KEY solr_update (solr_update),
  KEY hloc (hloc)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 PACK_KEYS=0;
");

$sth->execute;

}

